#!/bin/bash
/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
use online_edu_dwb;
insert into online_edu_dwb.dwb_signup_info_table
with tmp as
   ( select
        fcr.customer_id,
        dc.area,
        cast(fcr.anticipat_signup_date as varchar) as anticipat_signup_date,
        if(fca.appeal_status is null,0,appeal_status) as appeal_status,
        fcr.create_date_time,
        fcr.deleted,
        fcr.origin_type,
        fcr.origin_channel,
        fcr.payment_state,
        fcr.payment_time,
        fcr.creator as employee_id,
        de.scrm_department_id as tdepart_id,
        dsd.name as tdepart_name,
        fcr.itcast_clazz_id as class_id,
        dic.itcast_school_id,
        dic.itcast_school_name,
        dic.itcast_subject_id,
        dic.itcast_subject_name,
        "year",
        substring(fcr.month_year,6,2) as month,
        "day"
from online_edu_dwd.fact_customer_relationship as fcr
    left join online_edu_dwd.fact_customer_appeal as fca
        on fcr.id=fca.id and fca.deleted <> 1 and fcr.deleted <> 1
    left join online_edu_dwd.dim_customer as dc
        on fcr.customer_id=dc.id and dc.deleted <> 1
    left join online_edu_dwd.dim_itcast_clazz as dic
        on fcr.itcast_clazz_id=dic.id and dic.deleted <> 1
    left join online_edu_dwd.dim_employee as de
        on fcr.creator=de.id  and de.deleted <> 1
    left join online_edu_dwd.dim_scrm_department as dsd
        on de.scrm_department_id =dsd.id and dsd.deleted <> 1)
select *
from tmp;
"